/***************************************************************
*  Copyright (c) 2007 by GroupMe! Team (www.groupme.net)
*  All rights reserved.
*
*  This file is part of the GroupMe! Project. Source code of 
*  this project is closed and redistribution of this code is
*  prohibited. 
*  
*  Contact: http://www.groupme.net
*
*  This copyright notice MUST APPEAR in all copies of the file!
***************************************************************/
package net.groupme.utils.sql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import net.groupme.exceptions.InsertFailedException;
import net.groupme.model.Group;
import net.groupme.model.MODEL_CONSTANTS;
import net.groupme.model.ObjectFactory;
import net.groupme.model.ObjectFactoryProperties;
import net.groupme.model.OnlineStatus;
import net.groupme.model.Resource;
import net.groupme.model.User;
import net.groupme.model.dao.DATABASE_CONSTANTS;

/**
 * created on 18 Dec 2007
 * 
 * This class provides some static utility methods for querying user-specific attributes.
 * 
 * @author Fabian Abel, <a href="mailto:abel@l3s.de">abel@l3s.de</a>
 * @author last edited by: $Author: mischa $
 * 
 * @version $Revision: 1.7 $ $Date: 2009-08-05 09:19:00 $
 */
public class UserQueryUtility {
	
	/** the data source (= backend for DAO implementation) */
	static DriverManagerDataSource ds = new DriverManagerDataSource();
	static {
		ds.setDriverClassName(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_DRIVER")); //$NON-NLS-1$
		ds.setUrl(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_URL")); //$NON-NLS-1$
		ds.setUsername(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_USER")); //$NON-NLS-1$
		ds.setPassword(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_PW")); //$NON-NLS-1$
	}
	
	/** Logger for this class and subclasses */
	private static final Log logger = LogFactory.getLog(UserQueryUtility.class);
	
	/**
	 * Returns the password of the user as a HASH-value...
	 * @param username the GroupMe! username
	 * @return the password of the user as a HASH-value (as stored within the database)
	 */
	public static final String getUserPasswordAsHash(String username){
		Statement sqlStatement = null;
		ResultSet result = null;
		String password = null;
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			result = sqlStatement.executeQuery("Select " + DATABASE_CONSTANTS.USER_PASSWORD + " from " + DATABASE_CONSTANTS.TABLE_USER + " WHERE " + DATABASE_CONSTANTS.USER_USERNAME + " = '" + username + "' ");
			if(result.next()){
				password =  result.getString(1);
			}
			ObjectFactory.ds.getConnection().close();
			return password;
		} catch (SQLException e) {
			e.printStackTrace();
			logger.info("Failed to query database for encrypted password of " + username);
		}
		return null;
	}
	
	/**
	 * Returns <code>true</code> if there is a row within the User table of the database
	 * that contains the given username and the given password (as hash).
	 * @param username the GroupMe! username
	 * @param passwordAsHash user's password as Hash-value (as stored within the database)
	 * @return <code>true</code> if there is a row within the User table of the database
	 * that contains the given username and the given password (as hash), otherwise <code>false</code>.
	 */
	public static final boolean checkPasswordGivenAsHashValue(String username, String passwordAsHash){
		Statement sqlStatement = null;
		ResultSet result = null;
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			result = sqlStatement.executeQuery("Select * from " + DATABASE_CONSTANTS.TABLE_USER + " WHERE " + DATABASE_CONSTANTS.USER_USERNAME + " = '" + username + "' AND " + DATABASE_CONSTANTS.USER_PASSWORD + " = '" + passwordAsHash + "'");
			if(result.next()){
				ObjectFactory.ds.getConnection().close();
				return true;
			}else{
				ObjectFactory.ds.getConnection().close();
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.info("Failed to query database for encrypted password of " + username);
		}
		return false;
	}
	
	/**
	 * Returns <code>true</code> if the given credential (which orginates from
	 * the user's cookie) is equal to the one in the database.
	 * @param userId the user's id
	 * @param credential  credential, which in general orginates from  the user's cookie
	 * @return <code>true</code> if the given credential (which orginates from
	 * the user's cookie) is equal to the one in the database, otherwise <code>false</code>.
	 */
	public static final boolean checkCredential(String userId, String credential){
		Statement sqlStatement = null;
		ResultSet result = null;
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			result = sqlStatement.executeQuery("Select * from " + DATABASE_CONSTANTS.TABLE_USER_STATUS + " WHERE " + DATABASE_CONSTANTS.USER_STATUS_USER_ID + " = '" + userId + "' AND " + DATABASE_CONSTANTS.USER_STATUS_CREDENTIAL + " = '" + credential + "'");
			if(result.next()){
				ObjectFactory.ds.getConnection().close();
				return true;
			}else{
				ObjectFactory.ds.getConnection().close();
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.info("Failed to query database for credential of user.");
		}
		return false;
	}
	
	/**
	 * Returns <code>true</code> if the given user is either online or logged in
	 * via cookie, whose credential is given and has to be valid.
	 * @param userId the user's id
	 * @param credential  credential, which in general orginates from  the user's cookie
	 * @return <code>true</code> if the given user is either online or logged in
	 * via cookie, whose credential is given and has to be valid, otherwise <code>false</code>.
	 */
	public static final boolean isUserLoggedIn(String userId, String credential){
		Statement sqlStatement = null;
		ResultSet result = null;
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			result = sqlStatement.executeQuery("Select * from " + DATABASE_CONSTANTS.TABLE_USER_STATUS + 
					" WHERE " + DATABASE_CONSTANTS.USER_STATUS_USER_ID + " = '" + userId + 
					"' AND (( " + DATABASE_CONSTANTS.USER_STATUS_CREDENTIAL + " = '" + credential + "' " +
								"AND " + DATABASE_CONSTANTS.USER_STATUS_ONLINE_STATUS + " = '" + DATABASE_CONSTANTS.USER_STATUS_ONLINE_STATUS_LOGGED_IN_VIA_COOKIE + "' ) " +
						 " OR " + DATABASE_CONSTANTS.USER_STATUS_ONLINE_STATUS + " = '" + DATABASE_CONSTANTS.USER_STATUS_ONLINE_STATUS_ONLINE + "') ");
			if(result.next()){
				ObjectFactory.ds.getConnection().close();
				return true;
			}else{
				ObjectFactory.ds.getConnection().close();
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.info("Failed to query database for credential of user.");
		}
		return false;
	}
	
	/**
	 * Updates or inserts the new user's credential.
	 * @param userId id of the user
	 * @param credential the credential which is in general also stored in the user's cookie
	 * @throws InsertFailedException thrown if insert(update of database is not successful
	 */
	public static final void updateUserCredential(String userId, String credential) throws InsertFailedException{
		updateUserStatus(userId, null, credential);
	}
	
	/**
	 * Updates or inserts the new user's online status.
	 * @param userId id of the user
	 * @param status the online status of the user
	 * @throws InsertFailedException thrown if insert(update of database is not successful
	 */
	public static final void updateUserOnlineStatus(String userId, OnlineStatus status) throws InsertFailedException{
		updateUserStatus(userId, status, null);
	}
	
	/**
	 * Updates or inserts the new user's online status and credential.
	 * @param userId id of the user
	 * @param status the online status of the user
	 * @param credential the credential which is in general also stored in the user's cookie
	 * @throws InsertFailedException thrown if insert(update of database is not successful
	 */
	public static final void updateUserStatus(String userId, OnlineStatus status, String credential) throws InsertFailedException{
		boolean userIdExists = false;
		Statement sqlStatement = null;
		ResultSet result = null;
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			result = sqlStatement.executeQuery("Select * from " + DATABASE_CONSTANTS.TABLE_USER_STATUS + " WHERE " + DATABASE_CONSTANTS.USER_STATUS_USER_ID + " = '" + userId + "'");
			if(result.next()){
				userIdExists =  true;
			}
			ObjectFactory.ds.getConnection().close();
		} catch (SQLException e) {
			e.printStackTrace();
			logger.info("Failed to query database for credential of user.");
		}
		Object[] oa = null;
		SqlUpdate su = null;
		if(userIdExists){//update
			if(status == null && credential == null){
				throw new InsertFailedException("Status and Credential are null. Insert is not allowed.");
			}
			if(status != null){//update credential and online status
				su = new SqlUpdate(
						ObjectFactory.ds,
						"UPDATE " + DATABASE_CONSTANTS.TABLE_USER_STATUS + " SET " 
							+ DATABASE_CONSTANTS.USER_STATUS_ONLINE_STATUS + " = ?, " 
							+ DATABASE_CONSTANTS.USER_STATUS_CREDENTIAL + " = ?, " 
							+ DATABASE_CONSTANTS.USER_STATUS_TIMESTAMP + " = '" + new Timestamp(Calendar.getInstance().getTimeInMillis()).toString() + "' " +
								" WHERE " + DATABASE_CONSTANTS.USER_STATUS_USER_ID + " = ?");  
				su.declareParameter(new SqlParameter(DATABASE_CONSTANTS.USER_STATUS_ONLINE_STATUS, Types.VARCHAR));
				su.declareParameter(new SqlParameter(DATABASE_CONSTANTS.USER_STATUS_CREDENTIAL, Types.VARCHAR));
				su.declareParameter(new SqlParameter(DATABASE_CONSTANTS.USER_STATUS_USER_ID, Types.INTEGER));
				su.compile();
				//objects to insert:
				oa = new Object[3];
				oa[0] = status.getValue();
				oa[1] = (credential == null ? "NULL" : credential);
				oa[2] = new Integer(userId);
			}else if(status == null){//update credential
				su = new SqlUpdate(
						ObjectFactory.ds,
						"UPDATE " + DATABASE_CONSTANTS.TABLE_USER_STATUS + " SET " 
							+ DATABASE_CONSTANTS.USER_STATUS_CREDENTIAL + " = ?, " 
							+ DATABASE_CONSTANTS.USER_STATUS_TIMESTAMP + " = '" + new Timestamp(Calendar.getInstance().getTimeInMillis()).toString() + "' " +
								" WHERE " + DATABASE_CONSTANTS.USER_STATUS_USER_ID + " = ?");  
				su.declareParameter(new SqlParameter(DATABASE_CONSTANTS.USER_STATUS_CREDENTIAL, Types.VARCHAR));
				su.declareParameter(new SqlParameter(DATABASE_CONSTANTS.USER_STATUS_USER_ID, Types.INTEGER));
				su.compile();
				//objects to update:
				oa = new Object[2];
				oa[0] = (credential == null ? "NULL" : credential);
				oa[1] = new Integer(userId); 
			}			
		}else{//insert
			su = new SqlUpdate(ObjectFactory.ds, "INSERT INTO " + DATABASE_CONSTANTS.TABLE_USER_STATUS  //$NON-NLS-1$
					+ " (" + DATABASE_CONSTANTS.USER_STATUS_USER_ID + ", " + DATABASE_CONSTANTS.USER_STATUS_ONLINE_STATUS + ", "
					+ DATABASE_CONSTANTS.USER_STATUS_CREDENTIAL + ") VALUES (?, ?, ?)");
			su.declareParameter(new SqlParameter(DATABASE_CONSTANTS.USER_STATUS_USER_ID, Types.INTEGER)); 
			su.declareParameter(new SqlParameter(DATABASE_CONSTANTS.USER_STATUS_ONLINE_STATUS, Types.VARCHAR));
			su.declareParameter(new SqlParameter(DATABASE_CONSTANTS.USER_STATUS_CREDENTIAL, Types.VARCHAR)); 
			su.compile();
			//objects to insert:
			oa = new Object[3];
			oa[0] = new Integer(userId); 
			oa[1] = status.getValue();
			oa[2] = credential;
		}
		GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
		int count = su.update(oa, keyHolder);
		if (count == 0){
			throw new InsertFailedException();
		}
	}
	
	/**
	 * Returns the user's queue. It is a special group that contains resources the user wants to group in the future.
	 * @param userId id of the user
	 * @return the user's queue. It is a special group that contains resources the user wants to group in the future. Or <code>null</code> if such a queue does not exist yet.
	 */
	public static final Group getResourceQueueOfUser(String userId){
		Statement sqlStatement = null;
		ResultSet result = null;
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			String query = "Select " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " from " + DATABASE_CONSTANTS.TABLE_USERGROUP + 
			" WHERE " + DATABASE_CONSTANTS.USER_GROUP_USER_ID + " = '" + userId + 
			"' AND " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " in " +
					"(Select " + DATABASE_CONSTANTS.RESOURCE_ID + " from " + DATABASE_CONSTANTS.TABLE_RESOURCE + " WHERE " + DATABASE_CONSTANTS.RESOURCE_TYPE + "= '" + MODEL_CONSTANTS.RESOURCE_TYPE_QUEUE + "')";
			result = sqlStatement.executeQuery(query);
			if(result.next()){
				ObjectFactory.ds.getConnection().close();
				return ObjectFactory.getGroup(result.getString(1));
			}else{
				ObjectFactory.ds.getConnection().close();
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.info("Failed to query database for user's resource queue" );
		}
		return null;
	}
	
	
	/**
	 * This method returns the topK latest resources within the user's resource queue. 
	 * @return the topK latest resources within the user's resource queue.
	 */
	@SuppressWarnings("unchecked")
	public static List<Resource> getLatestResourcesWithinQueueOfUser(User user, int topK){
		Statement sqlStatement = null;
		ResultSet result = null;
		List<Resource> resources = new ArrayList<Resource>();
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			String query = "SELECT " + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + " FROM " + DATABASE_CONSTANTS.TABLE_GROUPRESOURCE  + 
				" WHERE " + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + " in " +
						"(SELECT " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USERGROUP + 
						" WHERE " + DATABASE_CONSTANTS.USER_GROUP_USER_ID + " = '" + user.getId() + "' AND " + 
						DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " in " +
								"(SELECT " + DATABASE_CONSTANTS.RESOURCE_ID + " FROM "+ DATABASE_CONSTANTS.TABLE_RESOURCE + " WHERE " + DATABASE_CONSTANTS.RESOURCE_TYPE + "='" + MODEL_CONSTANTS.RESOURCE_TYPE_QUEUE + "')) " + 
				" order by " + DATABASE_CONSTANTS.USER_GROUP_TAD+ " desc limit " + topK;
			result = sqlStatement.executeQuery(query);
			while(result.next()){
				resources.add(ObjectFactory.getResource(result.getString(1)));
			}
			ObjectFactory.ds.getConnection().close();
		}
		catch (SQLException e) {
			e.printStackTrace();
			logger.info("Failed to query database for resources within user's resource queue" );
		}
		return resources;
	}
	
	public static List<User> get6LatestUsersWithPhoto(){
		String query = "SELECT " + DATABASE_CONSTANTS.USER_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USER + 
		" WHERE " + DATABASE_CONSTANTS.USER_PHOTO+ " != '" + "' order by " + DATABASE_CONSTANTS.USER_TAD+ " desc limit 6";
		GetUsersQuery getUsers = new GetUsersQuery(ds, query, DATABASE_CONSTANTS.USER_ID);
		Iterator iter = getUsers.execute().iterator();
		if (iter.hasNext()) {
			return (List<User>) getUsers.execute().iterator().next();
		}
		return new ArrayList<User>();
	}
	
	public static List<User> getAllUsersWithPhoto(){
		String query = "SELECT " + DATABASE_CONSTANTS.USER_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USER + 
		" WHERE " + DATABASE_CONSTANTS.USER_PHOTO + " != ''";
		GetUsersQuery getUsers = new GetUsersQuery(ds, query, DATABASE_CONSTANTS.USER_ID);
		Iterator iter = getUsers.execute().iterator();
		if (iter.hasNext()) {
			return (List<User>) getUsers.execute().iterator().next();
		}
		return new ArrayList<User>();
	}
	
	/**
	 * Returns a list of user who match the given query pattern
	 * @param pattern pattern for username, firstname or lastname.
	 * @return a list of user who match the given query pattern
	 */
	public static List<User> searchForUser(String pattern){
		String query = "SELECT id, username, firstname, lastname, email FROM " + DATABASE_CONSTANTS.TABLE_USER + 
		" WHERE " +
		" username like \"%" + pattern + "%\" OR " +
		" firstname like \"%" + pattern + "%\" OR " +
		" lastname like \"%" + pattern + "%\" ";	;
		List<User> users = new ArrayList<User>();
		ResultSet result = SelectQueryUtility.executeQuery(query);
		try {
			while(result.next()){
				users.add(ObjectFactory.getInMemoryUser(result.getString(1), result.getString(2), result.getString(3), result.getString(4), result.getString(5)));				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}
}
